package DAO.MySQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.Driver;
import java.util.ArrayList;


public abstract class GenericDAO <EntityType> {
	
	//Connection Stuff
	private static Connection connection;
	private static Statement st;
	
	//Open Connection
	private static void openConnection() throws ClassNotFoundException, SQLException
	{
		//Step1:
		Class.forName("com.mysql.jdbc.Driver");
		//Step 2:
		connection = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/leaveapplication",
				"root",
				"password"
		);
		//Step 3:
		st = connection.createStatement();
	}
	
	//Close Connection
	private static void closeConnection() throws SQLException {
		//Step 6: Close Connections
		if (st != null)
		{ st.close(); }
		if (connection != null)
		{ connection.close(); }
	}
	
	//Execute a query for multiple results
	protected final ArrayList<EntityType> executeMultipleQuery(String SQLquery)
		throws SQLException, ClassNotFoundException
	{
		ArrayList<EntityType> list = new ArrayList<EntityType>();
		try {
			openConnection();
			//Step 4: Execute SQL
			ResultSet rs = st.executeQuery(SQLquery);
			//Step 5: Parse Result Set
			while(rs.next()) {
				EntityType tempE = parseResultSet(rs);
				list.add(tempE);
			}
		} finally {
			closeConnection();
		}
		return list;
	}
	
	//Execute Query for Single
	protected final String executeOneCellQuery(String SQLquery)
		throws SQLException, ClassNotFoundException
	{
		String output = null;
		try {
			openConnection();
			//Step 4: Execute SQL
			ResultSet rs = st.executeQuery(SQLquery);
			//Step 5: Parse Result Set
			if(rs.next()) {
				output = rs.getString(1);
			}
		} finally {
			closeConnection();
		}
		return output;		
	}

	/* This Abstract method is to be implemented in Child Class, to convert 1 row of ResultSet
	 *   into an appropriate Entity Object. It will be used by the Parent Method to generate the 
	 *   ArrayList of Entity Objects.
	 */
	protected abstract EntityType parseResultSet(ResultSet rs) throws SQLException;
	
	//Execute a Single Line Update
	protected final void executeSingleUpdate(String SQLstatement) throws SQLException, ClassNotFoundException 
	{ executeMultipleUpdate(1 , SQLstatement); }
	
	//Execute Multi-Line Update
	protected final void executeMultipleUpdate(int expectedRowsAffected, String SQLstatement) throws SQLException, ClassNotFoundException 
	{
		try {
			openConnection();
			connection.setAutoCommit(false);						//Transaction
			//Step 4: Execute SQL
			int affectedRows = st.executeUpdate( SQLstatement );
			//Step 5: Handle Errors
			if (affectedRows == expectedRowsAffected)		//If rows are correct
			{ connection.commit(); }										//Commit
			else																		//Otherwise rows are wrong
			{ 
				connection.rollback(); 										//Roll Back
				throw new SQLException(								//Throw Exception
					affectedRows + " Rows affected when " + expectedRowsAffected + 
					" was expected for Statement: " + SQLstatement
				);
			};
		} finally {
			closeConnection();
		}
	}
}
